package com.liu.dao.impl;

import com.liu.dao.IUserDao;
import com.liu.pojo.User;
import com.liu.util.LoggerFactory;

import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author 老刘Matthew
 * @Timestamp 2022/7/13 21:46
 * @Description
 */
public class UserDaoImpl extends GenericDaoImpl<User> implements IUserDao, LoggerFactory {

    public User queryByUsername(String username) throws SQLException {

        String where = "where user_name = '" + username + "'";

        if (0 == queryCount("user", where))
            return null;
        else
            return query(where).get(0);
    }

    @Override
    public Integer update(User user) throws SQLException {

        String sql = //
            "update user set sex = ?, self_info = ?, qq = ?, phone = ? where user_name = ?";

        return jdbcTemplate.updateOrInsert(sql,
                new Object[]{                   //
                        user.getSex(),          //
                        user.getSelf_info(),    //
                        user.getQq(),           //
                        user.getPhone(),        //
                        user.getUser_name()     //
                },
                new Integer[]{
                        Types.VARCHAR,        //
                        Types.VARCHAR,        //
                        Types.VARCHAR,        //
                        Types.VARCHAR,        //
                        Types.VARCHAR         //
                });
    }

    @Override
    public Integer updatePassword(User user) throws SQLException {

        String sql = //
            "update user set pwd = ?, email= ? where user_name = ?";

        return jdbcTemplate.updateOrInsert(sql,
                new Object[]{                   //
                        user.getPwd(),          //
                        user.getEmail(),        //
                        user.getUser_name(),    //
                },
                new Integer[]{
                        Types.VARCHAR,        //
                        Types.VARCHAR,        //
                        Types.VARCHAR,        //
                });
    }

    @Override
    public Integer updatePhoto(String photo, Integer user_id) throws SQLException {

        String sql = //
                "update user set photo = ? where user_id= ?";

        return jdbcTemplate.updateOrInsert(sql,
                new Object[]{
                        photo,
                        user_id
                },
                new Integer[]{
                        Types.VARCHAR,
                        Types.INTEGER
                });
    }

    @Override
    public String queryPhoto(Integer user_id) throws SQLException {

        String sql = //
                "select photo from user where userid =" + user_id;

        return jdbcTemplate.queryObject(sql, resultSet -> {

            User o = new User();

            while (resultSet.next()) {
                o.setPhone(resultSet.getString("photo"));
            }

            return o;

        }).getPhoto();
    }

    @Override
    public Integer save(User user) throws SQLException {

        String sql = //
                "insert into user(" + //
                    "user_name, " + //
                    "pwd, " + //
                    "email, " + //
                    "user_code," + //
                    "user_money" + //
                    ") " + //
                "values (?, ?, ?, ?, ?)";

        return jdbcTemplate.updateOrInsert(sql,
                new Object[]{
                        user.getUser_name(),    //
                        user.getPwd(),            //
                        user.getEmail(),        //
                        user.getUser_code(),    //
                        user.getUser_money()    //
                },
                new Integer[]{
                        Types.VARCHAR,        //
                        Types.VARCHAR,        //
                        Types.VARCHAR,        //
                        Types.INTEGER,        //
                        Types.INTEGER        //
                });
    }

    @Override
    public List<User> query(String where) throws SQLException {

        String sql =
                "select " + //
                        "user_id, user_name, pwd, email, sex, photo, self_info, qq, phone ,register_time, grade, user_code, user_money " +
                        "from user "
                        + appendWhere(where);

        return jdbcTemplate.query(sql, resultSet -> {

            List<User> list = new ArrayList<>();

            while (resultSet.next()) {

                User o = new User();
                o.setUser_id(resultSet.getInt("user_id"));
                o.setUser_name(resultSet.getString("user_name"));
                o.setPwd(resultSet.getString("pwd"));
                o.setEmail(resultSet.getString("email"));
                o.setSex(resultSet.getString("sex"));
                o.setPhoto(resultSet.getString("photo"));
                o.setSelf_info(resultSet.getString("self_info"));
                o.setQq(resultSet.getString("qq"));
                o.setPhone(resultSet.getString("phone"));
                o.setRegister_time(resultSet.getDate("register_time"));
                o.setGrade(resultSet.getInt("grade"));
                o.setUser_code(resultSet.getInt("user_code"));
                o.setUser_money(resultSet.getInt("user_money"));

                list.add(o);
            }

            return list;
        });
    }

    @Override
    public List<User> queryAll() throws SQLException {
        return query(null);
    }

    @Override
    public User querySingle(Integer id) {

        return null;
    }

    @Override
    public Integer queryCount(String table, String where) throws SQLException {

        String sql = "select count(0) c from " + table + " " + appendWhere(where);

        return jdbcTemplateCount.queryObject(sql, resultSet -> {

            resultSet.next();
            
            return resultSet.getInt("c");
        });
    }



}
